package com.gjm.tool.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class FileClose {

    private static String closeCustSql = "update  ctc_cc_cust_info_t set cust_manage_status= '3' where  cust_id='%s';";

    public static void main(String[] args) throws Exception {
//        closeTxt();
        check();
        System.out.println("程序结束");

    }

    public static void check() throws Exception{
        List<String> onList = new ArrayList<>();
        List<String> unList = new ArrayList<>();
        //我侧提供的数据
        List<List<Object>> ownerError = ownerError();
        List<String> ownerBusinessCodeList = new ArrayList<>();

        for (int i = 0; i < ownerError.size(); i++) {
            List<Object> row =  ownerError.get(i);
            String businessCode = String.valueOf(row.get(2));
            String custId = String.valueOf(row.get(0));
            ownerBusinessCodeList.add(businessCode);
        }

        //获取脚本中的内容
        List<String> list = FileUtil.readInfoFromFile("C:\\Users\\Administrator\\Desktop\\司库\\统一社会信用代码不可以刷数的数据.txt");
        for (String line : list) {
            List<String> businessCodeList = find("(?<=主数据businessCode：)([^,]+)", line);
            if (CollectionUtil.isEmpty(businessCodeList)){
                continue;
            }
            if (ownerBusinessCodeList.contains(businessCodeList.get(0))){
                onList.add(businessCodeList.get(0));
            }else {
                unList.add(businessCodeList.get(0));
            }
        }

        System.out.println("我侧提供的表格包含数量:" +onList.size() + "---" + unList.size());


    }


    public static void closeTxt(){
        /**
         * 1.查询可关闭的数据
         *   1.1 查询主数据所有的整改范围内数据
         *   1.2 通过客户客商编码匹配我侧数据
         *   1.3 整合计费+订单所有存在的业务数据，存储客户id
         *   1.4 将1.2结果去掉1.3结果=可关闭数据
         *   1.5 检查业务代码，客户关闭后，是否存在问题
         *   1.6 生产不可关闭集合
         */

        String file1 = "C:\\Users\\Administrator\\Desktop\\司库\\可以关闭的数据--客户管理全量 - 主数据提供 - 包含业务的--统一社会信用编码异常数据.txt";
        String file2 = "C:\\Users\\Administrator\\Desktop\\司库\\可以关闭的数据--客户管理统一社会编码异常 - 主数据提供 - 包含业务的--统一社会信用编码异常数据.txt";

        FileUtil.clearInfoForFile(file1);


        //计费+订单涉及业务的custId
        Set<String> businessCustIdSet = new HashSet<>();
        businessCustIdSet.addAll(getOrderCustId());
        businessCustIdSet.addAll(getBillCustId());


        //主数据数据
        List<List<Object>> mainData = mainDataWithSheet0();
        List<String> mainBusinessCodeList = new ArrayList<>();
        for (List<Object> row : mainData) {
            String mainBusinessCode = String.valueOf(row.get(1));
            mainBusinessCodeList.add(mainBusinessCode);
        }

        StringBuffer buffer1 = new StringBuffer();
        List<List<Object>> mysql = mysql();
        //我侧数据，以客户编码为主维度
        for (int i = 0; i < mysql.size(); i++) {
            List<Object> row =  mysql.get(i);
            String businessCode = String.valueOf(row.get(0));
            String custId = String.valueOf(row.get(5));
            //主数据存在的 或者 存在业务的，不处理
            if (mainBusinessCodeList.contains(businessCode) || businessCustIdSet.contains(custId)){
                continue;
            }
            System.out.println("第："+i);
            buffer1.append(String.format(closeCustSql,custId));
            buffer1.append("\n");
        }
        FileUtil.appendInfoToFile(file1,buffer1.toString());

        StringBuffer buffer2 = new StringBuffer();
        List<List<Object>> ownerError = ownerError();
        //我侧数据，以客户编码为主维度
        for (int i = 0; i < ownerError.size(); i++) {
            List<Object> row =  ownerError.get(i);
            String businessCode = String.valueOf(row.get(2));
            String custId = String.valueOf(row.get(0));
            //主数据存在的 或者 存在业务的，不处理
            if (mainBusinessCodeList.contains(businessCode) || businessCustIdSet.contains(custId)){
                continue;
            }
            System.out.println("第："+i);
            buffer2.append(String.format(closeCustSql,custId));
            buffer2.append("\n");
        }
        FileUtil.appendInfoToFile(file2,buffer2.toString());
    }

    public static List<List<Object>> mysql() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\四个字段的全量数据.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(1, sheetList.size());
    }

    public static List<List<Object>> ownerError() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\不合规数据提取-包含客户名称--我侧提交的全量异常数据.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(1, sheetList.size());
    }



    public static List<List<Object>> mainDataWithSheet0() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 0);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }



    public static List<String> getOrderCustId() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\不合规数据提取-整理数据可反馈业支--订单反馈.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<String> custIdList = sheetList.subList(2, sheetList.size()).stream().filter(i -> "是".equals(String.valueOf(i.get(26)))).map(i -> String.valueOf(i.get(0))).collect(Collectors.toList());
        return custIdList;
    }

    public static List<String> getBillCustId() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\不合规数据提取-整理数据可反馈业支--计费反馈.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<String> custIdList = sheetList.subList(2, sheetList.size()).stream().filter(i -> "是".equals(String.valueOf(i.get(0)))).map(i -> String.valueOf(i.get(1))).collect(Collectors.toList());
        return custIdList;
    }


    public static Set<String> mainData( ) {
        HashSet<String>  set= new HashSet<>();
        String filePath = "C:\\Users\\Administrator\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 1);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<List<Object>> list = sheetList.subList(2, sheetList.size());
        List<MainVO> mainList = new ArrayList<>();
        for (List<Object> objects : list) {
            mainList.add(new MainVO(String.valueOf(objects.get(1)), String.valueOf(objects.get(2)), String.valueOf(objects.get(3))));
        }
        Map<String, List<MainVO>> map = mainList.stream().collect(Collectors.groupingBy(MainVO::getCustCode));
        for (Map.Entry<String, List<MainVO>> entry : map.entrySet()) {
            if (CollectionUtil.isEmpty(entry.getValue()) || entry.getValue().size() <= 1) {
                continue;
            }
            Set<String> nameSet = entry.getValue().stream().map(i -> i.getCustName()).collect(Collectors.toSet());
            Set<String> certNoSet = entry.getValue().stream().map(i -> i.getCertNo()).collect(Collectors.toSet());

            if (nameSet.size() > 1 || certNoSet.size() > 1) {
                for (MainVO mainVO : entry.getValue()) {
                    set.add(mainVO.getCustCode());
                    System.out.println(JSON.toJSONString(mainVO));
                }

            }
        }
        return set;
    }

    /**
     * @param regex 正则表达式字符串
     * @param str   要匹配的字符串
     * @return;
     */
    public static List<String> find(String regex, String str) {
        List<String> list = new ArrayList<>();
        if (StrUtil.isBlank(regex) || StrUtil.isBlank(str)) {
            return list;
        }
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            list.add(matcher.group());
        }
        return list;
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    static class MainVO {
        private String custCode;
        private String custName;
        private String certNo;
    }


}



